package com.example.demo.controller;

import com.example.demo.DatabaseHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StreamUtils;

import javax.servlet.http.HttpServletRequest;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping
public class ApiController {

    @GetMapping("/")
    public ResponseEntity<Void> redirectToLoginPage() {
        HttpHeaders headers = new HttpHeaders();
        headers.add("Location", "/login.html");
        return new ResponseEntity<>(headers, HttpStatus.FOUND);
    }

    // 同步 SimpleHttpServer 的静态资源服务能力：匹配带扩展名的一层路径，例如 /page1.html /app.js /style.css 等
    @GetMapping("/{file:.+}")
    public ResponseEntity<byte[]> serveStaticFromClasspath(@PathVariable("file") String file) {
        try {
            // 从 classpath:static/ 读取资源（Spring Boot 打包后可用）
            ClassPathResource resource = new ClassPathResource("static/" + file);
            if (!resource.exists() || !resource.isReadable()) {
                String notFound = "404 - 找不到文件: /" + file;
                return ResponseEntity.status(HttpStatus.NOT_FOUND)
                        .header("Content-Type", "text/plain; charset=UTF-8")
                        .body(notFound.getBytes(StandardCharsets.UTF_8));
            }
            byte[] content;
            try (InputStream is = resource.getInputStream()) {
                content = StreamUtils.copyToByteArray(is);
            }
            HttpHeaders headers = new HttpHeaders();
            headers.add("Content-Type", getContentType(file));
            return new ResponseEntity<>(content, headers, HttpStatus.OK);
        } catch (Exception e) {
            String msg = "读取文件失败: " + e.getMessage();
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .header("Content-Type", "text/plain; charset=UTF-8")
                    .body(msg.getBytes(StandardCharsets.UTF_8));
        }
    }

    // ---------------------- /api/data ----------------------
    @GetMapping("/api/data")
    public ResponseEntity<Map<String, Object>> getData(HttpServletRequest request,
                                                       @RequestParam(value = "page", defaultValue = "page1") String page) {
        try {
            Map<String, String[]> parameterMap = request.getParameterMap();
            String userId = null; // 在方法开始处声明userId变量

            // 解析分页参数（兼容新旧参数名）
            int pageNum = parseIntOrDefault(getFirstParam(parameterMap, "pageNum", getFirstParam(parameterMap, "page_num", "1")), 1);
            int pageSize = parseIntOrDefault(getFirstParam(parameterMap, "pageSize", getFirstParam(parameterMap, "page_size", "10")), 10);

            // 检查按ID查询（只有当没有分页参数时才认为是ID查询）
            String idParam = getFirstParam(parameterMap, "id", null);
            boolean hasExplicitPageNum = parameterMap.containsKey("pageNum") || parameterMap.containsKey("page_num");
            if (idParam != null && idParam.trim().length() > 0 && !hasExplicitPageNum) {
                try {
                    // 处理可能的格式错误，如id=13?user_id=yonghu3
                    if (idParam.contains("?")) {
                        // 分割出实际的id值和其他参数
                        String[] parts = idParam.split("\\?", 2);
                        idParam = parts[0].trim();
                        
                        // 如果有其他参数，尝试解析
                        if (parts.length > 1 && parts[1].contains("=")) {
                            String[] queryParts = parts[1].split("=", 2);
                            if (queryParts.length == 2 && "user_id".equals(queryParts[0])) {
                                userId = queryParts[1];
                            }
                        }
                    }
                    
                    int id = Integer.parseInt(idParam);
                    List<Map<String, Object>> data = DatabaseHelper.getDataById(page, id);

                    Map<String, Object> pagination = new HashMap<>();
                    pagination.put("total", data.size());
                    pagination.put("totalPages", data.size() > 0 ? 1 : 0);
                    pagination.put("currentPage", 1);
                    pagination.put("pageSize", 10);

                    Map<String, Object> response = new HashMap<>();
                    response.put("success", true);
                    response.put("message", "查询成功");
                    response.put("data", data);
                    response.put("pagination", pagination);
                    return ResponseEntity.ok(response);
                } catch (NumberFormatException e) {
                    return errorResponse(400, "参数类型不匹配，参数id应为int类型");
                }
            }

            // 解析搜索参数
            Map<String, String> searchParams = new HashMap<>();
            
            for (Map.Entry<String, String[]> entry : parameterMap.entrySet()) {
                String key = entry.getKey();
                String value = entry.getValue() != null && entry.getValue().length > 0 ? entry.getValue()[0] : null;
                
                // 提取user_id参数
                if ("user_id".equals(key) && value != null && !value.trim().isEmpty()) {
                    userId = value.trim();
                }
                
                if (!"page".equals(key) && !"pageNum".equals(key) && !"pageSize".equals(key)
                        && !"page_num".equals(key) && !"page_size".equals(key) && !"id".equals(key)
                        && !"user_id".equals(key) && value != null && value.trim().length() > 0) {
                    searchParams.put(key, value.trim());
                }
            }

            // 构建搜索条件字符串（兼容 DatabaseHelper 现有方法）
            String search = "";
            if (!searchParams.isEmpty()) {
                StringBuilder sb = new StringBuilder();
                for (Map.Entry<String, String> entry : searchParams.entrySet()) {
                    if (sb.length() > 0) sb.append(" AND ");
                    sb.append(entry.getKey()).append("=").append(entry.getValue());
                }
                search = sb.toString();
            }

            int total = DatabaseHelper.getDataCount(page, search, userId);
            List<Map<String, Object>> data = DatabaseHelper.queryData(page, search, pageNum, pageSize, userId);
            int totalPages = (int) Math.ceil((double) total / pageSize);

            Map<String, Object> pagination = new HashMap<>();
            pagination.put("total", total);
            pagination.put("totalPages", totalPages);
            pagination.put("currentPage", pageNum);
            pagination.put("pageSize", pageSize);

            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("message", "查询成功");
            response.put("data", data);
            response.put("pagination", pagination);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            e.printStackTrace();
            
            // 根据异常类型返回更具体的错误信息
            if (e.getMessage().contains("表创建失败")) {
                return errorResponse(500, "数据库初始化失败，请检查数据库连接和权限: " + e.getMessage());
            } else if (e.getMessage().contains("Access denied")) {
                return errorResponse(500, "数据库访问权限不足: " + e.getMessage());
            } else if (e.getMessage().contains("doesn't exist")) {
                return errorResponse(500, "数据库或表不存在: " + e.getMessage());
            } else {
                return errorResponse(500, "查询失败: " + e.getMessage());
            }
        }
    }

    @PostMapping("/api/data")
    public ResponseEntity<Map<String, Object>> addData(@RequestParam(value = "page", defaultValue = "page1") String page,
                                                       @RequestBody Map<String, Object> data) {
        try {
            // 从JSON body中获取user_id
            String userId = (String) data.get("user_id");
            
            
            int newId = DatabaseHelper.addData(page, data, userId);
            Map<String, Object> response = new HashMap<>();
            if (newId > 0) {
                response.put("success", true);
                response.put("message", "添加成功");
                response.put("data", newId);
            } else {
                response.put("success", false);
                response.put("message", "添加失败");
            }
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            return errorResponse(500, "添加失败: " + e.getMessage());
        }
    }

    @PutMapping("/api/data")
    public ResponseEntity<Map<String, Object>> updateData(@RequestParam(value = "page", defaultValue = "page1") String page,
                                                          @RequestParam(value = "id", required = true) String idParam,
                                                          @RequestBody Map<String, Object> data) {
        try {
            // 处理可能的格式错误，如id=13?user_id=yonghu3
            String userId = null;
            if (idParam.contains("?")) {
                // 分割出实际的id值和其他参数
                String[] parts = idParam.split("\\?", 2);
                idParam = parts[0].trim();
                
                // 如果有其他参数，尝试解析
                if (parts.length > 1 && parts[1].contains("=")) {
                    String[] queryParts = parts[1].split("=", 2);
                    if (queryParts.length == 2 && "user_id".equals(queryParts[0])) {
                        userId = queryParts[1];
                    }
                }
            }
            
            try {
                int id = Integer.parseInt(idParam);
                if (id <= 0) {
                    return errorResponse(400, "缺少有效的ID参数");
                }
                
                // 如果URL中有user_id，优先使用URL中的，否则从JSON body中获取
                if (userId == null) {
                    userId = (String) data.get("user_id");
                } else {
                    // 确保data中也有正确的user_id
                    data.put("user_id", userId);
                }
                
                boolean success = DatabaseHelper.updateData(page, id, data);
                Map<String, Object> response = new HashMap<>();
                response.put("success", success);
                response.put("message", success ? "更新成功" : "更新失败");
                return ResponseEntity.ok(response);
            } catch (NumberFormatException e) {
                return errorResponse(400, "参数类型不匹配，参数id应为int类型");
            }
        } catch (Exception e) {
            return errorResponse(500, "更新失败: " + e.getMessage());
        }
    }

    @DeleteMapping("/api/data")
    public ResponseEntity<Map<String, Object>> deleteData(@RequestParam(value = "page", defaultValue = "page1") String page,
                                                          @RequestParam(value = "id", required = true) String idParam,
                                                          @RequestParam(value = "user_id", required = false) String userId) {
        try {
            // 处理可能的格式错误，如id=13?user_id=yonghu3
            if (idParam.contains("?")) {
                // 分割出实际的id值和其他参数
                String[] parts = idParam.split("\\?", 2);
                idParam = parts[0].trim();
                
                // 如果有其他参数，尝试解析
                if (parts.length > 1 && parts[1].contains("=")) {
                    String[] queryParts = parts[1].split("=", 2);
                    if (queryParts.length == 2 && "user_id".equals(queryParts[0])) {
                        userId = queryParts[1];
                    }
                }
            }
            
            try {
                int id = Integer.parseInt(idParam);
                if (id <= 0) {
                    return errorResponse(400, "缺少有效的ID参数");
                }
                
                boolean success = DatabaseHelper.deleteData(page, id);
                Map<String, Object> response = new HashMap<>();
                response.put("success", success);
                response.put("message", success ? "删除成功" : "删除失败");
                return ResponseEntity.ok(response);
            } catch (NumberFormatException e) {
                return errorResponse(400, "参数类型不匹配，参数id应为int类型");
            }
        } catch (Exception e) {
            return errorResponse(500, "删除失败: " + e.getMessage());
        }
    }

    // ---------------------- /api/upload-excel ----------------------
    @PostMapping(value = "/api/upload-excel")
    public ResponseEntity<Map<String, Object>> uploadExcel(@RequestParam(value = "page", defaultValue = "page1") String page,
                                                           @RequestParam("file") MultipartFile file,
                                                           @RequestParam(value = "user_id", required = false) String userId) {
        try {
            if (file == null || file.isEmpty()) {
                return errorResponse(400, "文件内容为空");
            }
            
            String fileName = file.getOriginalFilename();
            if (fileName == null || (!fileName.toLowerCase().endsWith(".xlsx") && !fileName.toLowerCase().endsWith(".xls"))) {
                return errorResponse(400, "请上传有效的Excel文件(.xlsx或.xls格式)");
            }

            // 添加调试日志
            System.out.println("开始解析Excel文件: " + fileName + ", 大小: " + file.getSize() + " bytes");
            
            List<Map<String, Object>> excelData = parseExcelFile(file.getBytes(), fileName, page, userId);
            System.out.println("Excel解析完成，解析到 " + excelData.size() + " 条记录");
            
            if (excelData.isEmpty()) {
                return errorResponse(400, "Excel文件不包含数据或格式不正确");
            }

            System.out.println("开始导入数据到数据库，用户ID: " + userId);
            Map<String, Integer> importResult = DatabaseHelper.importFromExcel(page, excelData, userId);
            System.out.println("数据库导入完成");
            int successCount = importResult.getOrDefault("success", 0);
            int duplicateCount = importResult.getOrDefault("duplicate", 0);

            Map<String, Object> importData = new HashMap<>();
            importData.put("success", successCount);
            importData.put("duplicate", duplicateCount);
            importData.put("total", excelData.size());

            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("message", "导入Excel数据完成");
            response.put("data", importData);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            // 记录详细的异常信息
            e.printStackTrace();
            
            String errorMessage = "导入Excel失败";
            if (e.getMessage() != null && !e.getMessage().trim().isEmpty()) {
                errorMessage += ": " + e.getMessage();
            } else {
                // 当异常信息为null时，提供异常类型信息
                errorMessage += ": " + e.getClass().getSimpleName();
                if (e.getCause() != null && e.getCause().getMessage() != null) {
                    errorMessage += " - " + e.getCause().getMessage();
                }
            }
            
            return errorResponse(500, errorMessage);
        }
    }

    // ---------------------- /api/import（JSON 批量导入） ----------------------
    @PostMapping("/api/import")
    public ResponseEntity<Map<String, Object>> batchImport(@RequestParam(value = "page", defaultValue = "page1") String page,
                                                           @RequestBody List<Map<String, Object>> dataList) {
        try {
            // 从第一条数据中获取user_id（假设所有数据都有相同的user_id）
            String userId = null;
            if (!dataList.isEmpty() && dataList.get(0).containsKey("user_id")) {
                userId = (String) dataList.get(0).get("user_id");
            }
            
            
            Map<String, Integer> importResult = DatabaseHelper.batchImport(page, dataList, userId);
            int successCount = importResult.getOrDefault("success", 0);
            int duplicateCount = importResult.getOrDefault("duplicate", 0);

            Map<String, Object> response = new HashMap<>();
            response.put("code", 200);
            response.put("msg", "导入完成");
            response.put("success", successCount);
            response.put("duplicate", duplicateCount);
            response.put("total", dataList != null ? dataList.size() : 0);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            return errorResponse(500, "导入失败: " + e.getMessage());
        }
    }

    // ---------------------- 辅助方法 ----------------------

    private String getFirstParam(Map<String, String[]> params, String key, String defaultValue) {
        if (params.containsKey(key) && params.get(key) != null && params.get(key).length > 0) {
            return params.get(key)[0];
        }
        return defaultValue;
    }

    private int parseIntOrDefault(String value, int defaultValue) {
        try {
            return Integer.parseInt(value);
        } catch (Exception e) {
            return defaultValue;
        }
    }

    private ResponseEntity<Map<String, Object>> errorResponse(int status, String message) {
        Map<String, Object> response = new HashMap<>();
        response.put("success", false);
        response.put("message", message);
        response.put("code", status);
        return ResponseEntity.status(status).body(response);
    }

    private List<Map<String, Object>> parseExcelFile(byte[] fileContent, String fileName, String page, String userId) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        try (InputStream is = new ByteArrayInputStream(fileContent)) {
            Workbook workbook;
            if (fileName.toLowerCase().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }

            try {
                Sheet sheet = workbook.getSheetAt(0);
                if (sheet == null) {
                    return result;
                }

                Row headerRow = sheet.getRow(0);
                if (headerRow == null) {
                    return result;
                }

            Map<Integer, String> headerMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    headerMap.put(i, cell.getStringCellValue().trim());
                }
            }

            Map<String, String> page1FieldMapping = new HashMap<>();
            page1FieldMapping.put("供应商", "supplier");
            page1FieldMapping.put("物料编码", "material_code");
            page1FieldMapping.put("物料名称", "material_name");
            page1FieldMapping.put("规格型号", "specification");
            page1FieldMapping.put("单位", "unit");
            page1FieldMapping.put("数量", "quantity");
            page1FieldMapping.put("批次", "batch_number");
            page1FieldMapping.put("到货日期", "arrival_date");
            page1FieldMapping.put("克隆号", "clone_number");
            page1FieldMapping.put("到期日期", "expiry_date");
            // 注意：用户ID不要求在Excel中，会自动设置
            
            Map<String, String> page2FieldMapping = new HashMap<>();
            page2FieldMapping.put("时间", "use_time");
            page2FieldMapping.put("实验号", "material_code");
            page2FieldMapping.put("分组", "group_names");
            page2FieldMapping.put("项目", "details");
            // 注意：用户ID不要求在Excel中，会自动设置

            Map<String, String> fieldMapping = "page1".equals(page) ? page1FieldMapping : page2FieldMapping;

            boolean hasRequiredColumns = true;
            for (Map.Entry<String, String> entry : fieldMapping.entrySet()) {
                boolean found = false;
                for (String headerTitle : headerMap.values()) {
                    if (headerTitle != null && headerTitle.contains(entry.getKey())) {
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    hasRequiredColumns = false;
                }
            }
            if (!hasRequiredColumns) {
                return result;
            }

            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                boolean isEmptyRow = true;
                for (int cellNum = 0; cellNum < headerMap.size(); cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        isEmptyRow = false;
                        break;
                    }
                }
                if (isEmptyRow) continue;

                Map<String, Object> rowData = new HashMap<>();
                
                // 注意：用户ID将在batchImport方法中统一添加，这里不需要添加
                
                for (int cellNum = 0; cellNum < headerMap.size(); cellNum++) {
                    String headerTitle = headerMap.get(cellNum);
                    if (headerTitle == null) continue;

                    // 跳过用户ID列，因为会在后续统一设置
                    if (headerTitle.contains("用户ID") || headerTitle.contains("user_id")) {
                        continue;
                    }
                    
                    String dbFieldName = null;
                    for (Map.Entry<String, String> entry : fieldMapping.entrySet()) {
                        if (headerTitle.contains(entry.getKey())) {
                            dbFieldName = entry.getValue();
                            break;
                        }
                    }
                    if (dbFieldName == null) continue;

                    Cell cell = row.getCell(cellNum);
                    if (cell == null) {
                        // 对于日期字段，使用null；其他字段使用空字符串或默认值
                        if (dbFieldName.contains("date") || dbFieldName.contains("time") || "use_time".equals(dbFieldName)) {
                            rowData.put(dbFieldName, null);
                        } else {
                            // 物料编码为空时使用 "-" 占位，数量为空时使用 0
                            if ("material_code".equals(dbFieldName)) {
                                rowData.put(dbFieldName, "-");
                            } else if ("quantity".equals(dbFieldName)) {
                                rowData.put(dbFieldName, 0.0);
                            } else {
                                rowData.put(dbFieldName, "");
                            }
                        }
                        continue;
                    }

                    switch (cell.getCellType()) {
                        case STRING:
                            String stringValue = cell.getStringCellValue();
                            // 如果是日期字段且为空字符串，则设置为null
                            if ((dbFieldName.contains("date") || dbFieldName.contains("time") || "use_time".equals(dbFieldName)) && 
                                (stringValue == null || stringValue.trim().isEmpty())) {
                                rowData.put(dbFieldName, null);
                            } else {
                                // 处理空字符串的特殊字段
                                if (stringValue == null || stringValue.trim().isEmpty()) {
                                    if ("material_code".equals(dbFieldName)) {
                                        rowData.put(dbFieldName, "-");
                                    } else if ("quantity".equals(dbFieldName)) {
                                        rowData.put(dbFieldName, 0.0);
                                    } else {
                                        rowData.put(dbFieldName, stringValue);
                                    }
                                } else {
                                    // 处理details字段，支持特定项目字段并替换中文逗号为英文逗号
                                    if ("details".equals(dbFieldName)) {
                                        // 替换中文逗号为英文逗号
                                        stringValue = stringValue.replace('，', ',');
                                    }
                                    rowData.put(dbFieldName, stringValue);
                                }
                            }
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                rowData.put(dbFieldName, dateFormat.format(cell.getDateCellValue()));
                            } else {
                                // 对于quantity字段，始终保留小数
                                if ("quantity".equals(dbFieldName)) {
                                    rowData.put(dbFieldName, cell.getNumericCellValue());
                                } else if (cell.getNumericCellValue() == Math.floor(cell.getNumericCellValue())) {
                                    rowData.put(dbFieldName, (int) cell.getNumericCellValue());
                                } else {
                                    rowData.put(dbFieldName, cell.getNumericCellValue());
                                }
                            }
                            break;
                        case BOOLEAN:
                            rowData.put(dbFieldName, cell.getBooleanCellValue());
                            break;
                        case FORMULA:
                            try {
                                rowData.put(dbFieldName, cell.getStringCellValue());
                            } catch (Exception ex) {
                                try {
                                    rowData.put(dbFieldName, cell.getNumericCellValue());
                                } catch (Exception ignored) {
                                    rowData.put(dbFieldName, "");
                                }
                            }
                            break;
                        case BLANK:
                            // 对于日期字段，使用null；其他字段使用空字符串或默认值
                            if (dbFieldName.contains("date") || dbFieldName.contains("time") || "use_time".equals(dbFieldName)) {
                                rowData.put(dbFieldName, null);
                            } else if ("material_code".equals(dbFieldName)) {
                                rowData.put(dbFieldName, "-");
                            } else if ("quantity".equals(dbFieldName)) {
                                rowData.put(dbFieldName, 0.0);
                            } else {
                                rowData.put(dbFieldName, "");
                            }
                            break;
                        default:
                            rowData.put(dbFieldName, "");
                    }
                }
                result.add(rowData);
            }

            } finally {
                workbook.close();
            }
        }
        
        return result;
    }

    // 根据文件扩展名获取Content-Type（同步 SimpleHttpServer 的逻辑）
    private String getContentType(String path) {
        if (path.endsWith(".html")) {
            return "text/html; charset=UTF-8";
        } else if (path.endsWith(".css")) {
            return "text/css; charset=UTF-8";
        } else if (path.endsWith(".js")) {
            return "application/javascript; charset=UTF-8";
        } else if (path.endsWith(".json")) {
            return "application/json; charset=UTF-8";
        } else if (path.endsWith(".png")) {
            return "image/png";
        } else if (path.endsWith(".jpg") || path.endsWith(".jpeg")) {
            return "image/jpeg";
        } else if (path.endsWith(".gif")) {
            return "image/gif";
        } else if (path.endsWith(".svg")) {
            return "image/svg+xml";
        } else {
            return "text/plain; charset=UTF-8";
        }
    }
} 